<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*, loginmysql.*"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<%!int formId;
	String icon;
	int unidad = 967;
	String fechaVieja;
	String horaVieja;
	String horaNueva;
	String fechaNueva;%>
<%
	//session = request.getSession(false);
	if (session == null || session.getAttribute("loggedIn") == null) {//logoogle
		//not logged
%><a href="FormLogin.jsp">Inicie Sesion</a>
<%
	} else if ((Integer) session.getAttribute("priv") == 1) {
%><a href="FormLogin.jsp">No tiene los privilegios necesarios</a>
<%
	} else {
		//logged
%>
<head>
<link rel="stylesheet" href="css/metro-bootstrap.css">
<link rel="stylesheet" href="css/modalcss.css">
<link href="css/iconFont.css" rel="stylesheet">
<script src="js/jquery-1.11.1.js"></script>
<script src="js/modal.js"></script>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Panel de Control</title>

<jsp:include page="Nav.html"></jsp:include>

<h1 style="text-align: center">

	<button onclick="location.href='Historial.jsp'"><</button>
	Historial
</h1>

</head>
<body class="metro">
	<sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver"
		url="jdbc:mysql://localhost/cot" user="root" password="Beyer9499" />
	<sql:setDataSource var="scar" driver="com.mysql.jdbc.Driver"
		url="jdbc:mysql://192.168.2.3/admprod" user="fira"
		password="firabeyer" />
	<%
		if (request.getParameter("form") == null) {

			} else {
				//unidad= request.getParameter("unidad");
				System.out.println(request.getParameter("form"));
				formId = Integer.parseInt(request.getParameter("form"));
			}
	%>


	<sql:query dataSource="${snapshot}" var="result">
SELECT yn, item.info as itin, observ.info, status.nombre from (itemdata, item, status) left join observ on (itemdata.Idid=observ.idid) where yn=sid and itemdata.Iid=item.Iid and itemdata.fid=<%=formId%>;
</sql:query>
	<table class="table hovered bordered" id="table">
		<thead>
			<tr>
				<th class="text-left">Item</th>
				<th class="text-left">Estado</th>
				<th class="text-left">Observaciones</th>
			</tr>
		</thead>
		<tbody>
			<c:forEach var="row" items="${result.rowsByIndex}">
				<c:choose>
					<c:when test="${row[0]>0}">
						<%
							icon = "<i class='icon-cancel-2 fg-red'></i>";
						%>
					</c:when>
					<c:when test="${row[0]==0}">
						<%
							icon = "<i class='icon-checkmark fg-green'></i>";
						%>
					</c:when>
					<c:otherwise>
						<%
							icon = "<i class='icon-minus-2 fg-grey'></i>";
						%>
					</c:otherwise>
				</c:choose>
				<tr>
					<td><c:out value="${row[1]}" /></td>
					<td><%=icon%> <c:out value="${row[3]}" /></td>
					<td><c:out value="${row[2]}" /></td>
				</tr>
			</c:forEach>
		</tbody>
	</table>

	<%
		java.sql.Connection conn = null;
			java.sql.Statement stmt = null;
			java.sql.Statement stmt2 = null;
			PreparedStatement prep = null;
			String sql;

			// JDBC driver name and database URL
			final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
			// static final String DB_URL = "jdbc:mysql://localhost/Cot";
			final String DB_URL = "jdbc:mysql://localhost/cot";
			// Database credentials
			// static final String USER = "root";
			final String USER = "root";
			final String PASS = "Beyer9499";

			try {
				Class.forName("com.mysql.jdbc.Driver");

				// STEP 3: Open a connection
				System.out.print("\nConnecting to database...");
				conn = DriverManager.getConnection(DB_URL, USER, PASS);
				System.out.println(" SUCCESS!\n");

				// STEP 5: Excute query
				System.out.print("\nInserting records into table...");
				stmt = conn.createStatement();

				sql = "Select Fecha, HoraControl from form where FormId="
						+ formId;
				ResultSet rs3 = stmt.executeQuery(sql);
				if (rs3.next()) {
					fechaNueva = "'" + String.valueOf(rs3.getDate("Fecha"))
							+ "'";
					horaNueva = "'"
							+ String.valueOf(rs3.getTime("HoraControl"))
							+ "'";
				}
				System.out.println(fechaNueva);
				System.out.println(horaNueva);

				sql = "Select uid from form where FormId=" + formId;
				ResultSet rs4 = stmt.executeQuery(sql);
				if (rs4.next()) {
					unidad = rs4.getInt("uid");
				}

				sql = "Select Fecha from form where uid=" + unidad
						+ " and TIMESTAMP(Fecha,HoraControl) < TIMESTAMP("
						+ fechaNueva + "," + horaNueva
						+ ") order by Fecha desc limit 1";
				System.out.println(sql);
				ResultSet rs2 = stmt.executeQuery(sql);
				if (rs2.next()) {
					fechaVieja = "'" + String.valueOf(rs2.getDate("Fecha"))
							+ "'";
				}
				System.out.println(fechaVieja);
				//select Fecha from form where uid=967 and Fecha < TIMESTAMP('${fechaRec}','${horaRec}') order by Fecha desc limit 0,1;
			} catch (SQLException se) {
				se.printStackTrace();
				System.out.println("NO SUCCESS!\n");
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					if (stmt != null)
						conn.close();
				} catch (SQLException se) {
				}
				try {
					if (conn != null)
						conn.close();
				} catch (SQLException se) {
					se.printStackTrace();
				}
			}
	%>

	<c:catch var="catchException">
	<sql:query dataSource="${scar}" var="result4">
Select per.PerNomPil, per.PerApe1, per.PerApe2, per.PerNro from per join asgreg on per.PerNro=asgreg.AsgCPerNro where asgreg.AsgUniNro=<%=unidad%>
and (date(asgreg.AsgFch) between <%=fechaVieja%> and <%=fechaNueva%> ) group by perNro
</sql:query>
	<h2 style="text-align: center">Chofer/es desde ultima inspeccion</h2>
	<table class="table hovered bordered" id='table'
		style='padding-top: 100px'>
		<thead>
			<tr>
				<th class="text-left">Nombre</th>
				<th class="text-left">Primer Apellido</th>
				<th class="text-left">Segundo Apellido</th>
			</tr>
		</thead>
		<tbody>
			<c:forEach var="row" items="${result4.rowsByIndex}">
				<tr>
					<td><c:out value="${row[0]}" /></td>
					<td><c:out value="${row[1]}" /></td>
					<td><c:out value="${row[2]}" /></td>
				</tr>
			</c:forEach>

		</tbody>
	</table>
	</c:catch>
	<c:if test="${catchException != null}">
		<p style='text-align:center'>
			Hubo un error al conectar con la base de datos.
		</p>
	</c:if>
</body>
</html>
<%
	}
%>